from sqlalchemy import (
    create_engine,
    MetaData,
    Table,
    Column,
    Integer,
    String,
    ForeignKey,
)
from pprint import pprint

engine = create_engine("mysql+pymysql://root:root@localhost/jobs")

# 创建元数据对象，保存表结构
metadata = MetaData()
# 定义users表
users = Table(
    "users",
    metadata,
    Column("id", Integer, primary_key=True),
    Column(
        "name",
        String(50),
    ),
    Column("fullname", String(100)),
)
# 用户的收货地址表
addresses = Table(
    "addresses",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("user_id", None, ForeignKey("users.id")),
    Column("email_address", String(100), nullable=False),
)
# 在数据库中创建所有的定义的
metadata.create_all(engine)

try:
    with engine.begin() as conn:
        conn.execute(
            users.insert(),
            [
                {"name": "zhangsan", "fullname": "zhang san"},
                {"name": "lisi", "fullname": "li si"},
            ],
        )
    print("数据插入成功")
except Exception as e:
    print(f"数据插入失败:{e}")

try:
    with engine.begin() as conn:
        result = conn.execute(users.select())
        for row in result:
            pprint(row)
    print("数据查询成功")
except Exception as e:
    print(f"数据查询失败:{e}")
